import datetime
import gc
import time
import os
import pymysql
import pdfplumber
import redis
import sys
import PyPDF2
import logging
from concurrent.futures import ThreadPoolExecutor


redis_conn = redis.Redis(host='gz-crs-3v22wfs1.sql.tencentcdb.com', port=23148, db=11, encoding='utf-8', decode_responses=True,password='j23QdKGB3js2',health_check_interval=30)
conn = pymysql.connect(host='tdsql-jjnggup1.sql.tencentcdb.com', port=121, user='spider',
                                       password='&8zk0EFq59J$',
                                       db='spiderdb')
cursor = conn.cursor()
sys.setrecursionlimit(10000)
# def insert_mysql(pdf, num, state):
    # text = pdf.pages[num].extract_text()
    # total_list = text.split("\n")
    # print("total_list: "+str(total_list))
    # if num == 0:
    #     top = 1
    # else:
    #     top = 0
    # for i in total_list[top:]:
    #     d = {}
    #     data_lsit = i.split(" ")
    #     print("data_lsit: "+str(data_lsit))
    #     num = redis_conn.llen("temp")
    #     if num > 0:
    #         del num
    #         gc.collect()
    #         content = str(redis_conn.lpop("temp"))
    #         name_of_the_entity = content+" ".join(data_lsit[2:-1])
    #         del content
    #         gc.collect()
    #     else:
    #         name_of_the_entity = " ".join(data_lsit[2:-1])
    #     if data_lsit[0].isdigit()==False:
    #         temp = " ".join(data_lsit)
    #         redis_conn.rpush("temp", temp)
    #     else:
    #         cin_or_fcrn = data_lsit[1]
    #         d["cin_or_fcrn"] = cin_or_fcrn
    #         d["name_of_the_entity"] = name_of_the_entity
    #         date = data_lsit[-1]
    #         d["date"] = date
    #         d["insert_time"] = insert_time
    #         d["update_time"] = update_time
    #         d["state"] = state
    #
            # try:
            #     sql = 'insert into mca_gov_in_pdf_data_list(cin_or_fcrn, name_of_the_entity, date_of_registration, insert_time, update_time, state) values("%s", "%s", "%s", "%s", "%s","%s")' % (cin_or_fcrn, name_of_the_entity, date, insert_time, update_time, state)
            #
            #     sql1 = 'select count(id) from mca_gov_in_pdf_data_list where cin_or_fcrn="{}"'.format(d["cin_or_fcrn"])
            #     cursor.execute(sql1)
            #     result = cursor.fetchall()[0][0]
            #     if result > 0:
            #         print("已存在数据库中")
            #         continue
            #     else:
            #         cursor.execute(sql)
            #         conn.commit()
            #         print(d)
            #         print("存放成功!")
            # except:
            #     try:
            #         print(i + "存入了redis!")
            #         redis_conn.rpush("in_pdf_data", str(data_lsit)+":"+state)
            #     except:
            #         with open("wrong.txt", 'a+')as f:
            #             f.write(str(data_lsit)+":"+state+"\n")
            #     finally:
            #         pass


def insert_mysql(file):
    state = " ".join(file.split("_")[:-2])
    print("state: " + state)
    pdffile = open('/usr/xy/project/India/pdf/{}'.format(file), 'rb')  # 读取pdf文件
    pdfreader = PyPDF2.PdfFileReader(pdffile)
    page_num = pdfreader.numPages  # 读取pdf页数======19
    for num in range(2569, page_num):
        page = pdfreader.getPage(num)  # 获取第1页，第一页是0
        l1 = page.extractText().split('\n')  # 返回的是字符串
        l2 = ([l1[i:i + 4] for i in range(0, len(l1), 4)])
        for i in l2[1:-1]:
            insert_time = datetime.datetime.now()
            update_time = datetime.datetime.now()
            d = {}
            d["cin_or_fcrn"] = i[1]
            d["name_of_the_entity"] = i[2]
            d["date"] = i[3]
            d["insert_time"] = insert_time
            d["update_time"] = update_time
            d["state"] = state
            print("现在是第【"+str(num+1)+"】页")
            print(d)

            try:
                sql = 'insert into mca_gov_in_pdf_data_list(cin_or_fcrn, name_of_the_entity, date_of_registration, insert_time, update_time, state) values("%s", "%s", "%s", "%s", "%s","%s")' % (d["cin_or_fcrn"], d["name_of_the_entity"], d["date"], insert_time, update_time, state)

                sql1 = 'select count(id) from mca_gov_in_pdf_data_list where cin_or_fcrn="{}"'.format(d["cin_or_fcrn"])
                cursor.execute(sql1)
                result = cursor.fetchall()[0][0]
                if result > 0:
                    print("已存在数据库中")
                    continue
                else:
                    cursor.execute(sql)
                    conn.commit()
                    # print(d)
                    print("存放成功!")
            except:
                try:
                    print(i + "存入了redis!")
                    redis_conn.rpush("in_pdf_data", str(i)+":"+state)
                except:
                    with open("wrong.txt", 'a+')as f:
                        f.write(str(i)+":"+state+"\n")
        del page
        gc.collect()

path = "/usr/xy/project/India/pdf"
dirs = os.listdir(path)
for file in dirs:
    # file = "Andaman_and_Nicobar_Islands_company_1.pdf"
    print(file)
    # LOG_FORMAT = "%(asctime)s - %(levelname)s - %(message)s"
    # logging.basicConfig(filename='my.log', level=logging.WARNING, format=LOG_FORMAT)
    # logging.info("state: "+state)
    insert_mysql(file)


    # pdf = pdfplumber.open('D:\\download\\pdf\\{}'.format(file))

    # # 提取第一页的本文内容
    # page_num = len(pdf.pages)
    # print("page_num: "+str(page_num))
    # # pool = ThreadPoolExecutor(max_workers=10, thread_name_prefix='test_')
    # for num in range(page_num):
    #     insert_mysql(pdf, num, state)
    #     # pool.submit(insert_mysql, pdf, num, state)
    # pdf.close()
    # del pdf
    # gc.collect()
conn.close()